using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

using DbOperation = SqlBlocks.ItemOperation<SqlBlocks.DatabaseOperationType>;

namespace SqlBlocks.Drivers
{
	public class SqlServer : IDatabaseDriver
	{
        string connectionString = null;
		#region IDatabaseDriver Members

		public void Commit(Changeset db, double version)
		{
			List<string> statements = BuildSql(db);
			SqlServer.AddVersionInformation(statements, version);

            try
            {
                SqlServerHelper.ExecuteNonQuery(this.connectionString, statements, true);
            }
            catch (SqlException sqlex)
            {
                string message = string.Format("The migration failed with the following error: {0}", sqlex.Message);
                throw new MigrationFailedException(message, sqlex);
            }
            catch (Exception ex)
            {
                string message = ex.Message;
                throw ex;
            }
		}

        public double CheckVersion(string connectionString)
        {
            return CheckVersion(connectionString, null);
        }

		public double CheckVersion(string connectionString, string group)
		{
            this.connectionString = connectionString;
            string versionKey = string.IsNullOrEmpty(group) ?
                "SqlBlocks_Version" :
                string.Format("SqlBlocks[{0}]_Version", group);

			string sql = @"
				    DECLARE @value sql_variant
				    SELECT @value = value FROM ::fn_listextendedproperty(
								    @versionKey, 
								    default, 
								    default, 
								    default, 
								    default, 
								    default, 
								    default
							    )
				    IF @value IS NULL
					    BEGIN
						    SET @value = 0
						    EXEC sys.sp_addextendedproperty
							    @name=@versionKey,
							    @value = 0
					    END

				    /*IF @value IS NOT NULL AND sql_variant_property(@value, 'BaseType') <> 'float'
					    BEGIN
						    SET @value = 0.00
						    EXEC sys.sp_updateextendedproperty
							    @name=N'SqlBlocks_Version',
							    @value = 0.00
					    END*/

				    SELECT CAST(@value as float)
			    ";
            using (SqlCommand cmd = new SqlCommand(sql))
            {
                cmd.Parameters.AddWithValue("@versionKey", versionKey);
                return (double)SqlServerHelper.ExecuteScalar(cmd, connectionString, true);
            }
		}

        public string DumpSchema(string connectionString)
        {
            return SqlServerHelper.ScriptDatabase(connectionString);
        }

        //public void LoadSchema(string connectionString, string schema)
        //{
        //    using (SqlConnection connection = new SqlConnection(connectionString))
        //    {
        //        SqlCommand cmd = new SqlCommand(schema, connection);
        //        try
        //        {
        //            cmd.Connection.Open();
        //            cmd.ExecuteNonQuery();
        //        }
        //        finally
        //        {
        //            cmd.Connection.Close();
        //        }
        //    }
        //}

		#endregion

		static List<string> BuildSql(Changeset db)
		{
			List<string> statements = new List<string>();

			foreach (DbOperation op in db.GetPendingOperations())
			{
				switch (op.Type)
				{
					case DatabaseOperationType.RawSql:
                        var sql = SqlServerHelper.Clean(op.GetData<string>());
                        statements.AddRange(sql);
						break;
					default:
						statements.Add("-- Unknown operation: " + op.Type);
						break;
				}
			}

			return statements;
		}

        //static string BuildTableSql(Table tbl)
        //{
        //    string sql = "";
        //    //string sql = string.Format(
        //    //    "{0} TABLE dbo.[{1}] (",
        //    //    tbl.Mode == Mode.Create ? "CREATE" : "ALTER",
        //    //    tbl.Name
        //    //    );

        //    OperationCollection<TableOperationType> columns = tbl.GetPendingOperations(
        //        TableOperationType.AddOrAlterColumn
        //        );

        //    List<string> cols = new List<string>(columns.Count);
        //    foreach (TblOperation c in columns)
        //    {
        //        string col = SqlServerHelper.GetColumnDefinition(c.GetData<Column>());
        //        cols.Add(col);
        //    }

        //    StringBuilder table = new StringBuilder("CREATE TABLE [");
        //    table.Append(tbl.Name).Append(']').Append(" (");

        //    for (int i = 0; i < cols.Count; i++)
        //    {
        //        if (i > 0) table.Append(", \r\n");
        //        table.Append(cols[i]);
        //    }
        //    table.Append(")");
        //    /*
        //    CREATE TABLE dbo.Items
        //    (
        //    ID int NOT NULL IDENTITY (1, 1),
        //    Name nvarchar(255) NOT NULL,
        //    Description nvarchar(MAX) NULL,
        //    Image nvarchar(255) NULL,
        //    [Key] nvarchar(255) NOT NULL
        //    )
        //     */
        //    sql = table.ToString();
        //    return sql;
        //}

		static void AddVersionInformation(List<string> statements, double version)
		{
			string ext = string.Format(@"EXEC sys.sp_updateextendedproperty 
			@name=N'SQLBlocks_Version', 
			@value={0}", string.Format("{0:0.0##}", version));

			statements.Add(ext);
		}
		
	}
}
